Option Strict On

Imports Excel = Microsoft.Office.Interop.Excel
'Imports Office = Microsoft.Office.Core
Imports System.Runtime.InteropServices
Imports System.Drawing

Public Class ReportEngine
    Private oldCulture As Globalization.CultureInfo
    Private Const defaultSearchSymbol As Char = "%"c
    Private Const defaultSearchPattern As String = defaultSearchSymbol & defaultSearchSymbol

    Public Sub New()
    End Sub

    Private Function CreateExcelApplication() As Excel.ApplicationClass
        SetEnglishCulture()
        Dim app As Excel.ApplicationClass = New Excel.ApplicationClass
        app.DisplayAlerts = False
        app.ScreenUpdating = False
        Return app
    End Function

    Private Sub CloseExcelApplication(ByVal ThisApplication As Excel.ApplicationClass)
        ThisApplication.Quit()
        ReleaseComObject(ThisApplication)
        SetOldCulture()
        ThisApplication = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub

    Private Sub SetEnglishCulture()
        oldCulture = Threading.Thread.CurrentThread.CurrentCulture()
        Threading.Thread.CurrentThread.CurrentCulture = New Globalization.CultureInfo("en-US")
    End Sub

    Private Sub SetOldCulture()
        If (Not oldCulture Is Nothing) Then
            Threading.Thread.CurrentThread.CurrentCulture = oldCulture
        End If
    End Sub


    Private Sub ReleaseFormulas(ByRef formulas As IList)
        Dim i As Integer
        For i = formulas.Count - 1 To 0 Step -1
            Dim f As ITemplateFormula = CType(formulas(i), ITemplateFormula)
            f.ReleaseFormula()
            formulas.Remove(f)
        Next
    End Sub

    Private Sub DeployFormulas(ByRef formulas As IList)
        Dim i As Integer
        For i = formulas.Count - 1 To 0 Step -1
            Dim f As ITemplateFormula = CType(formulas(i), ITemplateFormula)
            If (f.IsPreDeployed) Then
                f.DeployFormula()
                f.ReleaseFormula()
                formulas.Remove(f)
            End If
        Next
    End Sub

    Private Sub CheckFormulasIntersection(ByRef formulas As IList)
        Dim i As Integer
        Dim j As Integer
        For i = 0 To formulas.Count - 1
            Dim f As ITemplateFormula = CType(formulas(i), ITemplateFormula)
            If (Not f.IsPreDeployed) Then
                For j = 0 To formulas.Count - 1
                    Dim refFormula As ITemplateFormula
                    If (Not i = j) Then
                        refFormula = CType(formulas(j), ITemplateFormula)

                        If (refFormula.IsReferenceOnMe(f.ReferenceAddress) And Not refFormula.RowCount = -1) Then
                            f.PreDeployFormula(refFormula.RowCount)
                        End If
                    End If
                Next

            End If
        Next
    End Sub

    Private Sub EvaluateTemplateItems(ByVal workbook As Excel.Workbook, ByVal searchPattern As String, _
        ByVal propertyValues As Object, ByVal dSet As DataSet)

        For Each ws As Excel.Worksheet In workbook.Worksheets
            Dim rangeToSearch As Excel.Range = ws.Range("A:IV")
            Try

                Dim spacerList As IList = Spacers.FindSpacers(rangeToSearch, dSet)
                For Each s As ITemplateSpacer In spacerList
                    s.DeploySpacer()
                Next

                Dim propertiesList As IList = Properties.FindProperties(rangeToSearch, propertyValues)
                For Each p As ITemplateProperty In propertiesList
                    p.DeployProperty()
                Next

                Dim formulaTemplates As IList = Formulas.Find(rangeToSearch)
                Try
                    Dim tableList As IList = Tables.FindTables(rangeToSearch, dSet)

                    For Each t As ITemplateTable In tableList
                        t.DeployTable()
                        Dim i As Integer
                        For i = formulaTemplates.Count - 1 To 0 Step -1
                            Dim f As ITemplateFormula = CType(formulaTemplates(i), ITemplateFormula)
                            If (f.HasIntersection(t.ReferenceAddress) And Not f.IsPreDeployed) Then
                                f.PreDeployFormula(t.RowCount)
                            End If
                        Next
                    Next
                    CheckFormulasIntersection(formulaTemplates)
                    DeployFormulas(formulaTemplates)
                Finally
                    ReleaseFormulas(formulaTemplates)
                End Try
            Finally
                ReleaseComObject(rangeToSearch)
                ReleaseComObject(ws)
            End Try
        Next
    End Sub

    Private Sub GenerateDocument(ByVal ThisApplication As Excel.ApplicationClass, ByVal templatePath As String, ByVal resultPath As String, _
                                ByVal properties As Object, ByVal dSet As DataSet)

        Dim templateBook As Excel.Workbook = ThisApplication.Workbooks.Open(templatePath, False, False)
        Try

            Me.EvaluateTemplateItems(templateBook, defaultSearchPattern, properties, dSet)
            templateBook.SaveAs(resultPath)
        Finally
            For Each wb As Excel.Workbook In ThisApplication.Workbooks
                wb.Close()
            Next
            ReleaseComObject(templateBook)
            templateBook = Nothing
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End Try
    End Sub


    Public Sub GenerateDocument(ByVal templatePath As String, ByVal resultPath As String, _
                                ByVal properties As Object, ByVal dSet As DataSet)

        Dim ThisApplication As Excel.ApplicationClass = CreateExcelApplication()
        Try
            GenerateDocument(ThisApplication, templatePath, resultPath, properties, dSet)
        Finally
            CloseExcelApplication(ThisApplication)
        End Try
    End Sub


End Class
